Mastering SQL for Oracle Fusion HCM – Chapter 13. SQL Window Functions and Analytics in Fusion HCM

 

✅Chapter 13. SQL Window Functions and Analytics in Fusion HCM

SQL Window Functions and Analytics in Oracle Fusion HCM – Advanced Data Analysis Techniques

Learn SQL window functions and analytics for Oracle Fusion HCM. Discover how to use functions like ROW_NUMBER, RANK, and LEAD with real-world examples to analyze employee data.

🔹 Introduction

SQL window functions (also known as analytic functions) allow you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions that return a single result for multiple rows, window functions provide a result for each row, enabling detailed data analysis.

In Oracle Fusion HCM, window functions are invaluable for tasks such as:

  • Ranking employees based on their salary or performance.

  • Calculating running totals or moving averages over a specific period.

  • Analyzing employee trends over time.

This chapter covers the most common SQL window functions, including ROW_NUMBER, RANK, DENSE_RANK, LEAD, and LAG, along with practical examples in Fusion HCM.

🔹 Theoretical Concepts

📌 What is a Window Function?

A window function performs a calculation across a set of rows that are related to the current row. The result of a window function depends on the window frame — a subset of the data that defines the scope for the function.

📌 Syntax of Window Functions


SELECT column1, column2, 
       window_function() OVER (PARTITION BY column3 ORDER BY column4) AS window_result
FROM table_name;

📌 Key Clauses:

  1. PARTITION BY: Divides the data into partitions (groups of rows) to apply the window function to.

  2. ORDER BY: Defines the order in which the function is applied within each partition.

  3. ROWS BETWEEN: Specifies the window frame for the function.

📌 Common Window Functions

  1. ROW_NUMBER(): Assigns a unique number to each row, starting from 1.

  2. RANK(): Assigns a rank to each row within the partition, with gaps for ties.

  3. DENSE_RANK(): Similar to RANK(), but without gaps for ties.

  4. LEAD(): Provides access to the next row’s data.

  5. LAG(): Provides access to the previous row’s data.

🔹 Using Window Functions in SQL for Fusion HCM

✅ Example 1: ROW_NUMBER() for Ranking Employees by Salary

Scenario: Rank employees by their salary within each department.


SELECT person_number, full_name, department_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM per_all_assignments_m paam
JOIN hr_all_departments d
  ON paam.department_id = d.department_id;

Explanation: The ROW_NUMBER() function assigns a unique rank to each employee within their department based on their salary, with the highest salary ranked first.

✅ Example 2: RANK() to Rank Employees by Performance

Scenario: Rank employees by their performance score within each job role.


SELECT person_number,
       full_name,
       job_title,
       performance_score,
       RANK()
         over (
           PARTITION BY job_title
           ORDER BY performance_score DESC) AS performance_rank
FROM   per_all_people_f papf,
       per_all_assignments_m paam
WHERE  papf.person_id = paam.person_id 

Explanation: The RANK() function ranks employees within each job role by their performance score. Employees with the same score receive the same rank, but there is a gap in the ranking numbers for ties.

✅ Example 3: DENSE_RANK() for Department-wise Salary Ranks

Scenario: Rank employees by salary within each department, without gaps in the ranking for employees with the same salary.

SELECT person_number,
       full_name,
       department_name,
       salary,
       DENSE_RANK()
         over (
           PARTITION BY department_id
           ORDER BY salary DESC) AS dense_salary_rank
FROM   per_all_assignments_m paam,
       hr_all_departments d
WHERE  paam.department_id = d.department_id 

Explanation: The DENSE_RANK() function assigns ranks to employees by salary. Employees with the same salary get the same rank, but there are no gaps between ranks.

✅ Example 4: LEAD() to Compare Current Employee’s Salary with Next Employee’s Salary

Scenario: Compare each employee’s salary with the salary of the next employee within the same department.

SELECT person_number,
       full_name,
       department_name,
       salary,
       LEAD(salary, 1)
         over (
           PARTITION BY department_id
           ORDER BY salary DESC) AS next_salary
FROM   per_all_assignments_m paam,
       hr_all_departments d
WHERE  paam.department_id = d.department_id 

Explanation: The LEAD() function provides the salary of the next employee in the department when ordered by salary. If there is no next employee, the result is NULL.

✅ Example 5: LAG() to Compare Current Employee’s Salary with Previous Employee’s Salary

Scenario: Compare each employee’s salary with the salary of the previous employee within the same department.


SELECT person_number,
       full_name,
       department_name,
       salary,
       LAG(salary, 1)
         over (
           PARTITION BY department_id
           ORDER BY salary DESC) AS prev_salary
FROM   per_all_assignments_m paam,
       hr_all_departments d
WHERE  paam.department_id = d.department_id 

Explanation: The LAG() function retrieves the salary of the previous employee in the department, ordered by salary. If there is no previous employee, the result is NULL.

🔹 Real-Time Scenario (Fusion HCM Reporting)

🧾 Scenario:

“Generate a report showing the cumulative salary of employees in each department, with the running total updated for each employee’s salary.”

✅ SQL Query:


SELECT person_number,
       full_name,
       department_name,
       salary,
       SUM(salary)
         over (
           PARTITION BY department_id
           ORDER BY salary DESC ROWS BETWEEN unbounded preceding AND CURRENT ROW
         ) AS
       running_total
FROM   per_all_assignments_m paam,
       hr_all_departments d
WHERE  paam.department_id = d.department_id 

Explanation: The SUM() function is used with a window frame that calculates the running total of salaries for each department. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause specifies that the window frame starts from the first row of the partition (the first employee in the department) and includes all previous rows up to the current row.

🔹 Best Practices for Using Window Functions in Fusion HCM

  • Use PARTITION BY to break down data into logical groups, such as departments or job titles.

  • ORDER BY is crucial when you need to rank, compare, or calculate running totals.

  • Window functions can be performance-intensive. Test queries on smaller datasets before applying them to large data tables in Fusion HCM.

  • ROWS BETWEEN allows you to define custom window frames, such as moving averages or cumulative sums.

  • Combine window functions with JOINs to provide richer data analysis across multiple tables.

🔹 Summary

✅ SQL Window Functions enable advanced data analysis by providing row-specific results across partitions of data.
✅ Common functions like ROW_NUMBER, RANK, DENSE_RANK, LEAD, and LAG allow you to rank, compare, and calculate running totals within groups of data.
✅ Window functions are powerful tools in Fusion HCM reporting, useful for ranking employees, calculating performance, and analyzing trends over time.

🔹 Next Steps

Tags: #SQLWindowFunctions, #FusionHCM, #Lead, #Lag, #Rank, #DenseRank, #RowNumber, #RunningTotal, #SQLAnalytics

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)